import sqlalchemy
from sqlalchemy import Column, String, Integer, DateTime, extract, func
from utils.sql.db import session, Base
import time
from utils.sql.app.record import Man_day
from utils.sql.app.advance import Advance


# 统计
def statistics_data(isMonth, year, month, user_id):
    try:
        query = session.query(Man_day).filter(Man_day.user_id == user_id).all()
        if query:
            total = session.query(
                func.sum(Man_day.man_hour).label('total_hours'),
                func.sum(Man_day.over_time).label('total_overTime'),
                func.sum(Man_day.money_day).label('total_money'),
                func.sum(Man_day.overtime_pay).label('total_overTimePay'),
                func.sum(Man_day.working_days).label('total_working_days'),
            )
            # 预支
            advance = session.query(
                func.sum(Advance.advance_money).label('total_advance')
            )
            if isMonth is True:
                # 统计月
                total = total.filter(
                    Man_day.user_id == user_id,
                    extract('year', Man_day.job_date) == year,
                    extract('month', Man_day.job_date) == month
                ).one()
                # 预支
                advance = advance.filter(
                    Advance.user_id == user_id,
                    extract('year', Advance.advance_date) == year,
                    extract('month', Advance.advance_date) == month
                ).one()
            else:
                # 统计年
                total = total.filter(
                    Man_day.user_id == user_id,
                    extract('year', Man_day.job_date) == year
                ).one()
                # 预支
                advance = advance.filter(
                    Advance.user_id == user_id,
                    extract('year', Advance.advance_date) == year
                ).one()
                # print(total)
            if total.total_hours is not None:
                retuen_data = {
                    "total_hours": float(total.total_hours),
                    "total_overTime": float(total.total_overTime),
                    "total_money": float(total.total_money),
                    "total_overTimePay": float(total.total_overTimePay),
                    "total_working_days": float(total.total_working_days),
                    "total_advance": float(0)
                }
                if advance.total_advance is not None:
                    retuen_data['total_advance'] = float(advance.total_advance)

                return {
                    "code": 200,
                    "data": retuen_data,
                    "message": "查询成功"
                }
            else:
                return {
                    "code": 200,
                    "data": '',
                    "message": "数据为空"
                }
        else:
            return {
                "code": 200,
                "data": '',
                "message": "数据为空"
            }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "查询失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()
